# -*- coding: UTF-8 -*-
import mysql.connector as db
import sqlite3

def exportMicrofriend(mycursor):
	s3 = sqlite3.connect('sqlite3\\Microfriend.db')
	s3cursor = s3.cursor()
	s3cursor.execute("CREATE TABLE IF NOT EXISTS microfriends ( id INTEGER PRIMARY KEY AUTOINCREMENT, account TEXT NOT NULL UNIQUE, nick_name TEXT NOT NULL, age TEXT NOT NULL, address TEXT NOT NULL, height TEXT NOT NULL, education TEXT NOT NULL, gender TEXT NOT NULL, certification TEXT NOT NULL, job TEXT NOT NULL)")
	s3.commit()
	page = 0
	pageSize = 1000
	sqlSelect = "SELECT `account`, `nick_name`, `age`, `address`, `height`, `education`, `gender`, `certification`, `job` FROM `microfriends` LIMIT %d, %d"
	sqlInsert = "INSERT OR IGNORE INTO microfriends(account, nick_name, age, address, height, education, gender, certification, job) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)"
	while True:
		print("开始导出微交友数据第 %d 页" % page)
		mycursor.execute(sqlSelect % (pageSize * page, 1000))
		# for (account, nick_name, age, address, height, education, gender, certification, job) in mycursor.fetchall():
		rows = mycursor.fetchall()
		for row in rows:
			s3cursor.execute(sqlInsert, row)
		s3.commit()
		page += 1
		if len(rows) < pageSize:
			break
	print("微交友数据导出完成！")
def exportFollower(mycursor):
	s3 = sqlite3.connect('sqlite3\\follower.db')
	s3cursor = s3.cursor()
	s3cursor.execute("CREATE TABLE IF NOT EXISTS follower ( id INTEGER PRIMARY KEY AUTOINCREMENT, account TEXT NOT NULL UNIQUE, nick_name TEXT NOT NULL, gender TEXT NOT NULL DEFAULT '男', address TEXT NOT NULL, follower INTEGER NOT NULL, following INTEGER NOT NULL, last_weibo TEXT NOT NULL, level INTEGER NOT NULL, weibo_from TEXT NOT NULL, weibo_client TEXT NOT NULL, weibo_time TEXT NOT NULL, certification TEXT NOT NULL, following_account TEXT NOT NULL )")
	s3.commit()
	page = 0
	pageSize = 1000
	sqlSelect = "SELECT `account`,`nick_name`,`gender`,`address`,`follower`,`following`,`last_weibo`,`level`,`weibo_from`,`weibo_client`,`weibo_time`,`certification`,`following_account` FROM `follower` LIMIT %d, %d"
	sqlInsert = "INSERT OR IGNORE INTO follower(account, nick_name, gender, address, follower, following, last_weibo, level, weibo_from, weibo_client, weibo_time, certification, following_account) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
	# sqlInsert = "INSERT OR IGNORE INTO follower(account, nick_name, gender, address, follower, following, last_weibo, level, weibo_from, weibo_client, weibo_time, certification, following_account) VALUES('%s', '%s', '%s', '%s', %d, %d, '%s', %d, '%s', '%s', '%s', '%s', '%s', )"
	while True:
		print("开始导出听众数据第 %d 页" % page)
		mycursor.execute(sqlSelect % ( page * pageSize, pageSize))
		rows = mycursor.fetchall()
		for row in rows:
			# sql = sqlInsert % row
			# print(sql)
			s3cursor.execute(sqlInsert, row)
		s3.commit()
		page += 1
		if len(rows) < pageSize:
			break
	print('听众数据导出完成！')
if __name__ == '__main__':
	mydb = db.connect(user='root',db='collection',password='',host="127.0.0.1")
	mycursor = mydb.cursor()
	mycursor.execute("SET NAMES 'utf8'")
	mycursor.execute("SET SQL_MODE = 'TRADITIONAL'")
	# 导出微交友信息
	# exportMicrofriend(mycursor)
	# 导出听众数据
	exportFollower(mycursor)